package com.yc.easy.util;

import com.ly.common.util.Utils;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 要求: 表的主键必须是 id
 */
public class SqlBuilder {
    public static String buildInsert(String table, Map<String, Object> colMapVals, List<Object> params) {
        StringBuilder cols = new StringBuilder();
        StringBuilder vals = new StringBuilder();
        for (Map.Entry<String, Object> cv : colMapVals.entrySet()) {
            if (Utils.in(cv.getKey(), "db", "dbname")) {
                continue;
            }
            if (cols.length() > 0) {
                cols.append(",");
                vals.append(",");
            }
            cols.append(cv.getKey());
            vals.append("?");
            params.add(cv.getValue());
        }
        return String.format("insert into %s (%s) values (%s)", table, cols, vals);
    }

    public static String buildUpdateById(String table, Map<String, Object> colMapVals, List<Object> params) {
        if (!colMapVals.containsKey("id")) {
            throw new RuntimeException("buildUpdateById 要求必须提供 id 值!");
        }
        StringBuilder sets = new StringBuilder();
        for (Map.Entry<String, Object> cv : colMapVals.entrySet()) {
            if (Utils.in(cv.getKey(), "id", "db", "dbname")) {
                continue;
            }
            if (sets.length() > 0) {
                sets.append(",");
            }
            sets.append(cv.getKey() + "=?");
            params.add(cv.getValue());
        }
        params.add(colMapVals.get("id"));
        return String.format("update %s set %s where id=?", table, sets);
    }

    public static String buildDeleteById(String table, Object id, List<Object> params) {
        params.add(id);
        return String.format("delete from %s where id=?", table);
    }

    public static String buildSelectById(String table, Object id, List<Object> params) {
        params.add(id);
        return String.format("select * from %s where id=?", table);
    }

    /**
     * colMapVals.select => 自定义字段列表 缺省 *
     * colMapVals.join => 自定义join列表 join=uid=dm_user.id;pid=dm_product.id
     */
    public static String buildSelect(String table, Map<String, Object> colMapVals, List<Object> params) {
        String wheres = buildWhere(colMapVals, params);
        String select = nvl((String) colMapVals.get("select"), "*");
        String order = (String) colMapVals.get("order");
        order = order == null ? "" : (" order by " + order);
        table = buildJoin(table, colMapVals);
        return String.format("select %s\n  from %s where 1=1%s%s", select, table, wheres, order);
    }

    private static String buildJoin(String table, Map<String, Object> colMapVals) {
        String join = (String) colMapVals.get("join");
        StringBuilder sb = new StringBuilder(table + " a\n");
        if (join != null) {
            char alias = 'b';
            for (String s : join.split("[,;]")) {
                String[] joins = getGroups(s, "(.+)=(.+)\\.(.+)");
                String leftColumn = joins[0].matches("[a-z]\\..+") ? joins[0] : ("a." + joins[0]);
                String joinTable = joins[1];
                String joinColumn = joins[2];
                String joinSql = String.format("  join %s %s on %s=%s.%s\n", joinTable, alias, leftColumn, alias, joinColumn);
                alias++;
                sb.append(joinSql);
            }
        }
        return sb.toString();
    }

    private static String[] getGroups(String s, String regx) {
        Pattern p = Pattern.compile(regx);
        Matcher m = p.matcher(s);
        m.find();
        String[] ret = new String[m.groupCount()];
        for (int i = 0; i < m.groupCount(); i++) {
            ret[i] = m.group(i + 1);
        }
        return ret;
    }

    private static <T> T nvl(T... values) {
        for (T value : values) {
            if (value != null) return value;
        }
        return null;
    }

    public static String buildWhere(Map<String, Object> colMapVals, List<Object> params) {
        StringBuilder wheres = new StringBuilder();
        for (Map.Entry<String, Object> cv : colMapVals.entrySet()) {
            if ("join".equalsIgnoreCase(cv.getKey())
                    || "select".equalsIgnoreCase(cv.getKey())
                    || "order".equalsIgnoreCase(cv.getKey())
                    || "dbname".equalsIgnoreCase(cv.getKey())
                    || "db".equalsIgnoreCase(cv.getKey())) continue;
            wheres.append(" and ");
            if (cv.getKey().endsWith("_sql")) {
                wheres.append(cv.getValue());
                continue;
            }
            wheres.append((cv.getKey().matches("[a-z]\\..+") ? "" : "a.") + cv.getKey());
            if (cv.getValue() instanceof Collection) {
                wheres.append("in (");
                Collection coll = (Collection) cv.getValue();
                boolean first = true;
                for (Object o : coll) {
                    if (first == false) {
                        wheres.append(",");
                    } else {
                        first = false;
                    }
                    wheres.append("?");
                    params.add(o);
                }
                wheres.append(")");
                continue;
            } else if (cv.getValue() instanceof String && ((String) cv.getValue()).startsWith("%") && ((String) cv.getValue()).endsWith("%")) {
                wheres.append(" like ?");
            } else if (cv.getValue() == null || "null".equals(cv.getValue())) {
                wheres.append(" is null");
                continue;
            } else {
                wheres.append("=?");
            }
            params.add(cv.getValue());
        }
        return wheres.toString();
    }

}
